package dao;

import gui.PublicMethod;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import com.sun.org.apache.bcel.internal.generic.NEW;

import model.CommonList;
import model.Dorm;
import model.Meter;
import model.MeterCost;
import model.MeterPrice;
import model.ShortDate;

public class DaoImp_Meter implements IDao_Meter
{
	private DaoMethod method;
	private Connection conn;
	private CommonList common;

	public DaoImp_Meter()
	{
		conn = DaoMethod.getConnection();
		method = new DaoMethod();
		common = CommonList.getInstance();
	}

	public int setPrice(MeterPrice meterPrice)
	{
		String exe = "insert into meterprice(waterprice,powerprice,date_modify)values(?,?,?)";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(meterPrice.getWaterPrice());
		param.add(meterPrice.getPowerPrice());
		param.add(meterPrice.getDate_modify());
		return method.pstUpdate(exe, param);
	}

	public int addMeter(Meter meter)
	{
		String exe = "insert into meter(dorm_id,date_type_in,watervalue,powervalue)values(?,?,?,?)";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(common.getDormNumber().get(meter.getDormNumber()));
		param.add(meter.getDate_type_in());
		param.add(meter.getWaterValue());
		param.add(meter.getPowerValue());
		return method.pstUpdate(exe, param);
	}

	public Meter getMeterOfLastMonth(Meter meter)
	{
		String exe = "select id,dormNumber,date_type_in,watervalue,powervalue from meter_view where dormNumber='"
			+ meter.getDormNumber() + "' and date_type_in<'"+meter.getDate_type_in()+"' order by date_type_in desc limit 1";
		try
		{
			return getMeterList(exe).get(0);
		}
		catch (IndexOutOfBoundsException e)
		{
			return null;
		}
	}
	
	public List<Meter> getlastMeter(String dormNumber,Date date_type_in)
	{
		String exe = "select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where dormNumber='"
				+ dormNumber + "' and date_type_in<='"+date_type_in+"' order by date_type_in desc limit 2";
		try
		{
			return getMeterList(exe);
		}
		catch (IndexOutOfBoundsException e)
		{
			return null;
		}
	}

	public List<Meter> queryHistoryByDormNumber(String dormNumber,int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where dormNumber='"+dormNumber+"' and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where dormNumber='"+dormNumber+"' and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where dormNumber='"+dormNumber+"'";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Meter> queryHistoryByShortDate(String shortDate,int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where date_type_in='"+new ShortDate(shortDate).getDate()+"' and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where date_type_in='"+new ShortDate(shortDate).getDate()+"' and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where date_type_in='"+new ShortDate(shortDate).getDate()+"'";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Meter> queryAllHistory(int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where  isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where  isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Meter> queryHistoryByPower(int powerValue, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where powervalue='"+powerValue+"' and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where powervalue='"+powerValue+"' and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where powervalue='"+powerValue+"'";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Meter> queryHistoryByWater(int waterValue, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where watervalue='"+waterValue+"' and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where watervalue='"+waterValue+"' and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watervalue,powervalue,isuse from meter_view where watervalue='"+waterValue+"'";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}
	
	private List<Meter> getMeterList(String exe)
	{
		Statement st = null;
		ResultSet rs = null;
		List<Meter> list = new ArrayList<Meter>();
		try
		{
			st = conn.createStatement();
			rs = st.executeQuery(exe);
			if (rs.next())
			{
				do
				{
					Meter meter = new Meter();
					meter.setId(rs.getInt(1));
					meter.setDormNumber(rs.getString(2));
					meter.setDate_type_in(rs.getDate(3));
					meter.setWaterValue(rs.getInt(4));
					meter.setPowerValue(rs.getInt(5));
					meter.setIsuse(rs.getInt(6));
					list.add(meter);
				}
				while (rs.next());
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return list;
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			try
			{
				if (st != null)
				{
					st.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return list;
	}
	
	private List<MeterCost> getMeterCostList(String exe)
	{
		Statement st = null;
		ResultSet rs = null;
		List<MeterCost> list = new ArrayList<MeterCost>();
		try
		{
			st = conn.createStatement();
			rs = st.executeQuery(exe);
			if (rs.next())
			{
				do
				{
					MeterCost meterCost = new MeterCost();
					meterCost.setId(rs.getInt(1));
					meterCost.setDormNumber(rs.getString(2));
					meterCost.setShortDate(new ShortDate(rs.getDate(3)).getShortDate());
					meterCost.setWaterCost(rs.getFloat(4));
					meterCost.setPowerCost(rs.getFloat(5));
					meterCost.setPeopleDay(rs.getInt(6));
					meterCost.setIsuse(rs.getInt(7));
					list.add(meterCost);
				}
				while (rs.next());
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return list;
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			try
			{
				if (st != null)
				{
					st.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return list;
	}

	public int coverMeterRecord(Meter meter)
	{
		String exe = "update meter set watervalue=?,powervalue=? where id=?";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(meter.getWaterValue());
		param.add(meter.getPowerValue());
		param.add(meter.getId());
		return method.pstUpdate(exe, param);
	}

	public Vector<Vector<Object>> getTransRec(String dormNumber, Date thisTime,
			Date lastTime)
	{
		Vector<Vector<Object>> result = new Vector<Vector<Object>>();
		String exe = "select date_transition,new_people_count from transition_dorm where dorm_id=? and date_transition<=? and date_transition>? order by date_transition";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(common.getDormNumber().get(dormNumber));
		param.add(thisTime);
		param.add(lastTime);
		Vector<Vector<Object>> vec = method.pstQuery(exe, param);
		exe = "select date_transition,new_people_count from transition_dorm where dorm_id=? and date_transition<=? order by date_transition desc limit 0,1";
		param.remove(1);
		Vector<Vector<Object>> vec1 = method.pstQuery(exe, param);
		if (vec1.size() == 0)
		{
			result.add(null);
		}
		else
		{
			result.add(vec1.get(0));
		}
		result.addAll(vec);
		return result;
	}

	public void addMeterCostRecord(MeterCost meterCost)
	{
		String exe = "insert into metercost(dorm_id,date_type_in,peopleday,watercost,powercost)values(?,?,?,?,?)";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(common.getDormNumber().get(meterCost.getDormNumber()));
		param.add(new ShortDate(meterCost.getShortDate()).getDate());
		param.add(meterCost.getPeopleDay());
		param.add(meterCost.getWaterCost());
		param.add(meterCost.getPowerCost());
		method.pstUpdate(exe, param);
	}

	public void coverMeterCostRecord(MeterCost meterCost)
	{
		String exe="select id from metercost where dorm_id=? and date_type_in=?";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(common.getDormNumber().get(meterCost.getDormNumber()));
		param.add(new ShortDate(meterCost.getShortDate()).getDate());
		Vector<Vector<Object>> vector=method.pstQuery(exe, param);
		int id=(Integer)vector.get(0).get(0);
		exe = "update metercost set peopleday=?,watercost=?,powercost=? where id=?";
		param.clear();
		param.add(meterCost.getPeopleDay());
		param.add(meterCost.getWaterCost());
		param.add(meterCost.getPowerCost());
		param.add(id);
		method.pstUpdate(exe, param);
	}

	public List<MeterCost> queryAllCost(int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view";
			break;
		default:
			break;
		}
		return getMeterCostList(exe);
	}

	public List<MeterCost> queryCostByAverage(float average, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where (watercost+powercost)/peopleday="+average+"and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where (watercost+powercost)/peopleday="+average+"and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where (watercost+powercost)/peopleday="+average;
			break;
		default:
			break;
		}
		return getMeterCostList(exe);
	}

	public List<MeterCost> queryCostByDormNumber(String dormNumber, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where dormNumber="+dormNumber+"and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where dormNumber="+dormNumber+"and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where dormNumber="+dormNumber;
			break;
		default:
			break;
		}
		return getMeterCostList(exe);
	}

	public List<MeterCost> queryCostByPeopleDay(int peopleDay, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where peopleday="+peopleDay+"and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where peopleday="+peopleDay+"and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where peopleday="+peopleDay;
			break;
		default:
			break;
		}
		return getMeterCostList(exe);
	}

	public List<MeterCost> queryCostByPowerCost(float powerCost, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where powercost="+powerCost+"and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where powercost="+powerCost+"and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where powercost="+powerCost;
			break;
		default:
			break;
		}
		return getMeterCostList(exe);
	}

	public List<MeterCost> queryCostByShortDate(String shortDate, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where date_type_in="+new ShortDate(shortDate).getDate()+"and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where date_type_in="+new ShortDate(shortDate).getDate()+"and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where date_type_in="+new ShortDate(shortDate).getDate();
			break;
		default:
			break;
		}
		return getMeterCostList(exe);
	}

	public List<MeterCost> queryCostByTotalCost(float totalCost, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where watercost+powercost="+totalCost+"and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where watercost+powercost="+totalCost+"and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where watercost+powercost="+totalCost;
			break;
		default:
			break;
		}
		return getMeterCostList(exe);
	}

	public List<MeterCost> queryCostByWaterCost(float waterCost, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where watercost="+waterCost+"and isuse=0";
			break;
		case PublicMethod.out:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where watercost="+waterCost+"and isuse=1";
		case PublicMethod.both:
			exe="select id,dormNumber,date_type_in,watercost,powercost,peopleday,isuse from metercost_view where watercost="+waterCost;
			break;
		default:
			break;
		}
		return getMeterCostList(exe);
	}

	
}
